using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Web;
using System.Text;


namespace database
{
    
    /// <summary>
    /// Summary description for clsDataBase
    /// </summary>
    public class DAO_DATABASE  
    {

        private SqlConnection conn;
        private string strConnection;
        private List<SqlParameter> ListSqlParameter;

    

        public DAO_DATABASE()
        {
           // strConnection =  ConfigurationManager.ConnectionStrings["connDB"].ConnectionString;

            ListSqlParameter = new List<SqlParameter>();

            // TANKSPAR
             strConnection = "Data Source=dbsq0015.whservidor.com;Initial Catalog=tankspar1;Integrated Security=false; User=tankspar1; Password=@lanis28";
             
            // TANKSPAR LOCALHOST
          //  strConnection = @"Data Source=JUNIOR-PC\SQLEXPRESS;Initial Catalog=tankspar1;Integrated Security=True;";

            conn = new SqlConnection(strConnection);

        }



        private static bool DBConnectionStatus(string connection)
        {
            try
            {
                using (SqlConnection sqlConn = new SqlConnection(connection))
                {
                    sqlConn.Open();
                    return (sqlConn.State == ConnectionState.Open);
                }
            }
            catch (SqlException)
            {
                return false;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public void Open()
        {

            if (conn == null || (conn.State == ConnectionState.Closed))
            {

                try
                {
                    conn.Open();

                    DataTable dt = conn.GetSchema();

                    DataTable dt1 = conn.GetSchema("Tables");
                }
                catch (Exception)
                {

    
                }
            }

        }

        public void Close()
        {
            if (conn != null)
                conn.Close();

            if (ListSqlParameter != null && ListSqlParameter.Count > 0)
                ListSqlParameter.Clear();
        }


        private SqlCommand GetCommand(string query, CommandType commType)
        {
            SqlCommand objCommand = new SqlCommand(query, conn);
            objCommand.CommandTimeout = 300;
            objCommand.CommandType = commType;

            return objCommand;
        }


        private SqlCommand GetCommandQuery(string query)
        {
            return GetCommand(query, CommandType.Text);
        }

        private SqlCommand GetCommandProcedure(string query)
        {
            return GetCommand(query, CommandType.StoredProcedure);
        }

        public int ExecuteQueryUpdate(string query)
        {
            Open();
            SqlCommand objCommand = GetCommandQuery(query);

            int ret = 0;
            try
            {
                AddParameter(objCommand);
                ret = objCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                Close();
            }

            return ret;
        }


        public DataSet ExecuteQuery(string query)
        {

            Open();


            try
            {
                SqlCommand objCommand = GetCommandQuery(query);

                DataSet ds = new DataSet();

                SqlDataAdapter objDataAdapater = new SqlDataAdapter(objCommand);

                AddParameter(objCommand);

                objDataAdapater.Fill(ds);

                return ds;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                Close();
            }


        }


        public DataSet ExecuteProcedure(string query)
        {
            Open();
            try
            {
                SqlCommand objCommand = GetCommandProcedure(query);
                DataSet ds = new DataSet();
                SqlDataAdapter objDataAdapater = new SqlDataAdapter(objCommand);
                AddParameter(objCommand);
                objDataAdapater.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                Close();
            }

        }


        /// <summary>
        /// Cria parametros para a SP
        /// </summary>
        /// <param name="ParamName">Name of param.</param>
        /// <param name="DbType">Param type.</param>
        /// <param name="Value">Param value.</param>
        /// <returns>New parameter.</returns>
        public void CreateParameter(string paramName, SqlDbType dbType, object value, int size, bool UpperCase, ParameterDirection direction)
        {
            SqlParameter objParameter = new SqlParameter(paramName, dbType);

            if (value == null)
            {
                value = DBNull.Value;
            }

            objParameter.Value = value;

            if (size > -1)
                objParameter = new SqlParameter(paramName, dbType, size);
            else
                objParameter = new SqlParameter(paramName, dbType);

            objParameter.Value = value;

            objParameter.Direction = direction;

            ListSqlParameter.Add(objParameter);
        }

        /// <summary>
        /// Cria os Parametros da Procedure
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="dbType"></param>
        /// <param name="value"></param>
        public void CreateParameter(string paramName, SqlDbType dbType, object value)
        {
            CreateParameter(paramName, dbType, value, -1, true, ParameterDirection.Input);
        }

        private void AddParameter(SqlCommand command)
        {
            if (ListSqlParameter != null)
            {
                foreach (SqlParameter objParameter in ListSqlParameter)
                {
                    command.Parameters.Add(objParameter);
                }
            }
        }


        /// <summary>
        /// Verifica se o Dataset contem dados
        /// </summary>
        /// <param name="ds">Informe o Dataset</param>
        /// <returns>Bool</returns>
        public bool DataSetIsEmpty(DataSet ds)
        {
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }


    }
}